const db = require("../db")
const _ = require("lodash")
const ws = require("nodejs-websocket");
var timer;
module.exports = {
        getAllTypeNum: (req, res) => {
            let str = `select count(*) as users from user;select count(*) as goods from goods;select count(*) as orders from user_order;select sum(order_sumup) as allMoney from user_order;`
            db.query(str, function (err, result) {
                if (err) return res.cc(err)
                let data = {
                    users: result[0][0].users,
                    goods: result[1][0].goods,
                    orders: result[2][0].orders,
                    sum: result[3][0].allMoney
                }
                return res.send({
                    code: 200,
                    msg: "获取数据成功！",
                    data: data
                })
            })
        },
        getUserPie: (req, res) => {
            let str = 'select count(*) value,role.roleName from user,role where user.roleId = role.id group by roleId;'
            db.query(str, function (err, result) {
                if (err) return res.cc(err)
                let catalogue = []
                let data = []
                result.forEach(item => {
                    let temp = {
                        value: item.value,
                        name: item.roleName
                    }
                    data.push(temp)
                    catalogue.push(item.roleName)
                });
                return res.send({
                    code: 200,
                    msg: "获取数据成功！",
                    data: {
                        catalogue: catalogue,
                        data: data
                    }
                })

            })
        },
        getGoodsTop5: (req, res) => {
            let str = `select goodName,saleCount,round(saleCount*price,2) as sum from goods order by saleCount desc limit 0,5;`
            db.query(str, function (err, result) {
                if (err) return res.cc(err)
                let category = []
                let saleCount = []
                let sum = []
                for (let i = 0; i < result.length; i++) {
                    category.push(result[i].goodName)
                    saleCount.push(result[i].saleCount)
                    sum.push(result[i].sum)
                }
                return res.send({
                    code: 200,
                    msg: "获取数据成功！",
                    data: {
                        category: category,
                        sum: sum,
                        saleCount: saleCount
                    }
                })
            })
        },
        getShopTop5: (req, res) => {
            let str = `select shop.shopName round(sum(goods.saleCount*price),2) as sum from goods,shop where goods.shopId = shop.id group by goods.shopId order by sum desc;`
            db.query(str, function (err, result) {
                if (err) return res.cc(err)
                let category = []
                let sum = []
                for (let i = 0; i < result.length; i++) {
                    category.push(result[i].shopName)
                    sum.push(result[i].sum)
                }
                return res.send({
                    code: 200,
                    msg: "获取数据成功！",
                    data: {
                        category: category,
                        sum: sum
                    }
                })
            })
        },
        // 获取订单实时消息
        getOrderTimeLine: (req, res) => {
            let str = `select user.userName,shop.shopName,goods.goodName,user_order.createTime from user,shop,goods,user_order where user.id=user_order.userId and goods.id = user_order.goodId and shop.id = user_order.shopId limit 0 ,10`
            db.query(str, function (err, result) {
                if (err) return res.cc(err)
                return res.send({
                    code: 200,
                    msg: "获取数据成功！",
                    data: result
                })
            })
        },
        // 获取订单统计
        getOrderLine: (req, res) => {
            let str1 = `SELECT
        IFNULL(SUM(CASE HOUR(createTime) WHEN 0 THEN 1 ELSE 0 END),0)as '0',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 1 THEN 1 ELSE 0 END),0)as '1',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 2 THEN 1 ELSE 0 END),0) AS '2',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 3 THEN 1 ELSE 0 END),0) AS '3',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 4 THEN 1 ELSE 0 END),0) AS '4',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 5 THEN 1 ELSE 0 END),0) AS '5',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 6 THEN 1 ELSE 0 END),0) AS '6',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 7 THEN 1 ELSE 0 END),0) AS '7',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 8 THEN 1 ELSE 0 END),0) AS '8',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 9 THEN 1 ELSE 0 END),0) AS '9',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 10 THEN 1 ELSE 0 END),0) AS '10',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 11 THEN 1 ELSE 0 END),0) AS '11',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 12 THEN 1 ELSE 0 END),0) AS '12',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 13 THEN 1 ELSE 0 END),0) AS '13',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 14 THEN 1 ELSE 0 END),0) AS '14',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 15 THEN 1 ELSE 0 END),0) AS '15',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 16 THEN 1 ELSE 0 END),0) AS '16',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 17 THEN 1 ELSE 0 END),0) AS '17',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 18 THEN 1 ELSE 0 END),0) AS '18',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 19 THEN 1 ELSE 0 END),0) AS '19',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 20 THEN 1 ELSE 0 END),0) AS '20',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 21 THEN 1 ELSE 0 END),0) AS '21',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 22 THEN 1 ELSE 0 END),0) AS '22',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 23 THEN 1 ELSE 0 END),0) AS '23'
        FROM user_order
        WHERE date(createTime) >= CURDATE() and date(createTime) <= CURDATE();
        `
            let str2 = `SELECT

        IFNULL(SUM(CASE HOUR(createTime) WHEN 0 THEN 1 ELSE 0 END),0)as '0',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 1 THEN 1 ELSE 0 END),0)as '1',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 2 THEN 1 ELSE 0 END),0) AS '2',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 3 THEN 1 ELSE 0 END),0) AS '3',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 4 THEN 1 ELSE 0 END),0) AS '4',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 5 THEN 1 ELSE 0 END),0) AS '5',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 6 THEN 1 ELSE 0 END),0) AS '6',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 7 THEN 1 ELSE 0 END),0) AS '7',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 8 THEN 1 ELSE 0 END),0) AS '8',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 9 THEN 1 ELSE 0 END),0) AS '9',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 10 THEN 1 ELSE 0 END),0) AS '10',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 11 THEN 1 ELSE 0 END),0) AS '11',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 12 THEN 1 ELSE 0 END),0) AS '12',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 13 THEN 1 ELSE 0 END),0) AS '13',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 14 THEN 1 ELSE 0 END),0) AS '14',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 15 THEN 1 ELSE 0 END),0) AS '15',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 16 THEN 1 ELSE 0 END),0) AS '16',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 17 THEN 1 ELSE 0 END),0) AS '17',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 18 THEN 1 ELSE 0 END),0) AS '18',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 19 THEN 1 ELSE 0 END),0) AS '19',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 20 THEN 1 ELSE 0 END),0) AS '20',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 21 THEN 1 ELSE 0 END),0) AS '21',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 22 THEN 1 ELSE 0 END),0) AS '22',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 23 THEN 1 ELSE 0 END),0) AS '23'
        
        FROM user_order
        
        WHERE date(createTime) >= date_sub(CURDATE() , INTERVAL 1 DAY) AND date(createTime) < CURDATE()
        `
            db.query(str1, function (err, result) {
                if (err) return res.cc(err)
                let data = {
                    today: _.values(result[0]),
                    category: _.keys(result[0])
                }
                db.query(str2, function (err, result2) {
                    if (err) return res.cc(err)
                    data['yesterday'] = _.values(result2[0])
                    return res.send({
                        code: 200,
                        msg: "获取数据成功！",
                        data: data
                    })
                })
            })
        },
        // 统计今日用户增长情况
        getUserLine: (req, res) => {
            let str1 = `SELECT
        IFNULL(SUM(CASE HOUR(createTime) WHEN 0 THEN 1 ELSE 0 END),0)as '0',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 1 THEN 1 ELSE 0 END),0)as '1',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 2 THEN 1 ELSE 0 END),0) AS '2',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 3 THEN 1 ELSE 0 END),0) AS '3',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 4 THEN 1 ELSE 0 END),0) AS '4',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 5 THEN 1 ELSE 0 END),0) AS '5',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 6 THEN 1 ELSE 0 END),0) AS '6',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 7 THEN 1 ELSE 0 END),0) AS '7',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 8 THEN 1 ELSE 0 END),0) AS '8',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 9 THEN 1 ELSE 0 END),0) AS '9',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 10 THEN 1 ELSE 0 END),0) AS '10',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 11 THEN 1 ELSE 0 END),0) AS '11',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 12 THEN 1 ELSE 0 END),0) AS '12',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 13 THEN 1 ELSE 0 END),0) AS '13',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 14 THEN 1 ELSE 0 END),0) AS '14',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 15 THEN 1 ELSE 0 END),0) AS '15',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 16 THEN 1 ELSE 0 END),0) AS '16',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 17 THEN 1 ELSE 0 END),0) AS '17',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 18 THEN 1 ELSE 0 END),0) AS '18',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 19 THEN 1 ELSE 0 END),0) AS '19',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 20 THEN 1 ELSE 0 END),0) AS '20',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 21 THEN 1 ELSE 0 END),0) AS '21',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 22 THEN 1 ELSE 0 END),0) AS '22',
        IFNULL(SUM(CASE HOUR(createTime) WHEN 23 THEN 1 ELSE 0 END),0) AS '23'
        FROM user
        WHERE date(createTime) >= CURDATE() and date(createTime) <= CURDATE();
        `
            let str2 = `SELECT

        IFNULL(SUM(CASE HOUR(createTime) WHEN 0 THEN 1 ELSE 0 END),0)as '0',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 1 THEN 1 ELSE 0 END),0)as '1',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 2 THEN 1 ELSE 0 END),0) AS '2',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 3 THEN 1 ELSE 0 END),0) AS '3',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 4 THEN 1 ELSE 0 END),0) AS '4',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 5 THEN 1 ELSE 0 END),0) AS '5',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 6 THEN 1 ELSE 0 END),0) AS '6',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 7 THEN 1 ELSE 0 END),0) AS '7',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 8 THEN 1 ELSE 0 END),0) AS '8',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 9 THEN 1 ELSE 0 END),0) AS '9',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 10 THEN 1 ELSE 0 END),0) AS '10',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 11 THEN 1 ELSE 0 END),0) AS '11',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 12 THEN 1 ELSE 0 END),0) AS '12',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 13 THEN 1 ELSE 0 END),0) AS '13',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 14 THEN 1 ELSE 0 END),0) AS '14',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 15 THEN 1 ELSE 0 END),0) AS '15',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 16 THEN 1 ELSE 0 END),0) AS '16',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 17 THEN 1 ELSE 0 END),0) AS '17',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 18 THEN 1 ELSE 0 END),0) AS '18',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 19 THEN 1 ELSE 0 END),0) AS '19',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 20 THEN 1 ELSE 0 END),0) AS '20',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 21 THEN 1 ELSE 0 END),0) AS '21',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 22 THEN 1 ELSE 0 END),0) AS '22',
        
        IFNULL(SUM(CASE HOUR(createTime) WHEN 23 THEN 1 ELSE 0 END),0) AS '23'
        
        FROM user
        
        WHERE date(createTime) >= date_sub(CURDATE() , INTERVAL 1 DAY) AND date(createTime) < CURDATE()
        `
        db.query(str1, function (err, result) {
            if (err) return res.cc(err)
            let data = {
                today: _.values(result[0]),
                category: _.keys(result[0])
            }
            db.query(str2, function (err, result2) {
                if (err) return res.cc(err)
                data['yesterday'] = _.values(result2[0])
                // conn.sendText(JSON.stringify({
                //     code: 200,
                //     msg: "获取数据成功！",
                //     data: data
                // }))
                return res.send({
                    code:200,
                    msg:"获取数据成功！",
                    data:data
                })
            })
        })
            }
        }